using Microsoft.Extensions.Options;
using System.Data;
using System.Diagnostics;
using System.IO.Compression;
using MySql.Data.MySqlClient;
using Npgsql;
using System.Data.SQLite;
using System.Data.OleDb;
using Microsoft.Data.SqlClient;
using HtERP.Data;

namespace HtERP.Services
{
    /// <summary>
    /// 数据库备份服务实现
    /// </summary>
    public class DbBackupService : IDbBackupService
    {
        private readonly ILogger<DbBackupService> _logger;
        private readonly IOptionsMonitor<BackupSettings> _backupSettings;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="logger">日志记录器</param>
        /// <param name="backupSettings">备份设置</param>
        public DbBackupService(
            ILogger<DbBackupService> logger,
            IOptionsMonitor<BackupSettings> backupSettings)
        {
            _logger = logger;
            _backupSettings = backupSettings;
        }

        /// <summary>
        /// 异步执行数据库备份操作
        /// </summary>
        public async Task<BackupResult> BackupDatabaseAsync(string databaseType, string connectionString, string backupPath, bool compress = true, CancellationToken cancellationToken = default)
        {
            var result = new BackupResult();
            var stopwatch = Stopwatch.StartNew();

            try
            {
                _logger.LogInformation("开始数据库备份: 类型={DatabaseType}, 路径={BackupPath}", databaseType, backupPath);

                // 确保目标目录存在
                var directory = Path.GetDirectoryName(backupPath);
                if (!Directory.Exists(directory))
                {
                    Directory.CreateDirectory(directory);
                }

                // 解析连接字符串，构建备份计划配置
                var schedule = CreateBackupSchedule(databaseType, connectionString, backupPath);
                if (schedule == null)
                {
                    throw new Exception("无法解析连接字符串或构建备份计划");
                }

                // 执行备份操作
                string backupFilePath = await PerformBackupAsync(schedule);

                // 如果需要压缩
                if (compress && !backupFilePath.EndsWith(".zip", StringComparison.OrdinalIgnoreCase))
                {
                    var zipFilePath = Path.ChangeExtension(backupFilePath, ".zip");
                    using (var archive = ZipFile.Open(zipFilePath, ZipArchiveMode.Create))
                    {
                        archive.CreateEntryFromFile(backupFilePath, Path.GetFileName(backupFilePath));
                    }
                    
                    // 删除原始备份文件
                    File.Delete(backupFilePath);
                    backupFilePath = zipFilePath;
                }

                // 获取文件大小
                var fileInfo = new FileInfo(backupFilePath);
                result.FileSize = fileInfo.Length;
                result.BackupFilePath = backupFilePath;
                result.Success = true;
                result.Message = "数据库备份成功";

                _logger.LogInformation("数据库备份成功: 文件={BackupFilePath}, 大小={FileSize}字节", backupFilePath, result.FileSize);
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.ErrorMessage = ex.Message;
                result.Message = "备份失败: " + ex.Message;
                _logger.LogError(ex, "数据库备份失败: {ErrorMessage}", ex.Message);
            }
            finally
            {
                stopwatch.Stop();
                result.DurationMs = stopwatch.ElapsedMilliseconds;
            }

            return result;
        }

        /// <summary>
        /// 根据数据库类型和连接字符串创建备份计划配置
        /// </summary>
        private BackupSchedule CreateBackupSchedule(string databaseType, string connectionString, string backupPath)
        {
            var schedule = new BackupSchedule
            {
                Enabled = true,
                BackupDirectory = Path.GetDirectoryName(backupPath),
                AutoDeleteOlderThanDays = 30,
                DiskSpaceThresholdMB = 1024,
                // 设置自定义备份文件名（如果提供）
                CustomBackupFileName = Path.GetFileName(backupPath)
            };

            var dbConfig = new DbConnectionConfig
            {
                DatabaseType = databaseType
            };

            // 根据数据库类型解析连接字符串
            switch (databaseType.ToLower())
            {
                case "mysql":
                    ParseMySqlConnectionString(connectionString, dbConfig);
                    break;
                case "sqlserver":
                    ParseSqlServerConnectionString(connectionString, dbConfig);
                    break;
                case "postgresql":
                    ParsePostgreSqlConnectionString(connectionString, dbConfig);
                    break;
                case "sqlite":
                    ParseSqliteConnectionString(connectionString, dbConfig);
                    break;
                case "access":
                    ParseAccessConnectionString(connectionString, dbConfig);
                    break;
                default:
                    _logger.LogError("不支持的数据库类型: {DatabaseType}", databaseType);
                    return null;
            }

            schedule.DbConfig = dbConfig;
            return schedule;
        }

        /// <summary>
        /// 执行数据库备份
        /// </summary>
        private async Task<string> PerformBackupAsync(BackupSchedule schedule)
        {
            try
            {
                // 创建备份目录
                CreateBackupDirectory(schedule.BackupDirectory);

                // 检查磁盘空间
                if (CheckDiskSpace(schedule.BackupDirectory, schedule.DiskSpaceThresholdMB))
                {
                    _logger.LogWarning("磁盘空间不足，尝试清理旧备份文件...");
                    await CleanupOldBackupsAsync(schedule);
                }

                string backupFilePath;

                // 实现大小写不敏感的数据库类型识别
                string dbType = schedule.DbConfig.DatabaseType?.ToLowerInvariant() ?? string.Empty;

                if (string.IsNullOrEmpty(dbType))
                {
                    throw new NotSupportedException("数据库类型不能为空");
                }

                switch (dbType)
                {
                    case "mysql":
                    case "mariadb":
                        backupFilePath = await BackupMySQLAsync(schedule);
                        break;
                    case "postgresql":
                    case "pg":
                        backupFilePath = await BackupPostgreSQLAsync(schedule);
                        break;
                    case "sqlite":
                        backupFilePath = await BackupSQLiteAsync(schedule);
                        break;
                    case "sqlserver":
                    case "mssql":
                        backupFilePath = await BackupSQLServerAsync(schedule);
                        break;
                    case "access":
                        backupFilePath = await BackupAccessAsync(schedule);
                        break;
                    default:
                        throw new NotSupportedException($"不支持的数据库类型: {schedule.DbConfig.DatabaseType}");
                }

                return backupFilePath;
            }
            catch (Exception ex)
            {
                _logger.LogError("数据库备份失败: {ErrorMessage}", ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 备份MySQL数据库
        /// </summary>
        private async Task<string> BackupMySQLAsync(BackupSchedule schedule)
        {
            var config = schedule.DbConfig;
            string backupFileName;
            string backupFilePath;

            // 优先使用自定义备份文件名
            if (!string.IsNullOrEmpty(schedule.CustomBackupFileName))
            {
                backupFileName = schedule.CustomBackupFileName;
                // 确保文件扩展名为.sql
                if (!backupFileName.EndsWith(".sql", StringComparison.OrdinalIgnoreCase))
                {
                    backupFileName += ".sql";
                }
            }
            else
            {
                // 如果没有自定义文件名，使用原来的逻辑生成文件名
                string timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
                backupFileName = $"{config.DatabaseName}_backup_{timestamp}.sql";
            }
            
            backupFilePath = Path.Combine(schedule.BackupDirectory, backupFileName);

            try
            {
                // 创建mysqldump命令
                string args = $"--host={config.Server} --port={config.Port} --user={config.Username} --password={config.Password} --databases {config.DatabaseName} --result-file={backupFilePath}";

                if (!string.IsNullOrEmpty(config.AdditionalParams))
                {
                    args += " " + config.AdditionalParams;
                }

                try
                {
                    // 尝试执行mysqldump命令
                    await ExecuteCommandAsync("mysqldump", args);
                }
                catch (Exception ex)
                {
                    // 捕获mysqldump执行异常，记录日志
                    _logger.LogWarning("mysqldump命令执行失败: {ErrorMessage}，尝试使用代码方式备份MySQL数据库", ex.Message);
                    // 直接尝试代码备份方式，不再检查文件是否存在
                    await BackupMySQLByCodeAsync(config, backupFilePath);
                }

                // 检查备份文件是否成功生成
                if (!File.Exists(backupFilePath))
                {
                    _logger.LogInformation("mysqldump命令未找到或执行失败，使用代码方式备份MySQL数据库");
                    await BackupMySQLByCodeAsync(config, backupFilePath);
                }

                return backupFilePath;
            }
            catch (Exception ex)
            {
                _logger.LogError("MySQL备份失败: {ErrorMessage}", ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 使用代码方式备份MySQL数据库
        /// </summary>
        private async Task BackupMySQLByCodeAsync(DbConnectionConfig config, string backupFilePath)
        {
            string connectionString = $"server={config.Server};port={config.Port};user id={config.Username};password={config.Password};database={config.DatabaseName};";

            if (!string.IsNullOrEmpty(config.AdditionalParams))
            {
                connectionString += config.AdditionalParams;
            }

            using (var connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();

                // 获取所有表
                var tables = new List<string>();
                using (var cmd = new MySqlCommand("SHOW TABLES", connection))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            tables.Add(reader.GetString(0));
                        }
                    }
                }

                using (var writer = new StreamWriter(backupFilePath))
                {
                    // 创建数据库语句
                    await writer.WriteLineAsync($"CREATE DATABASE IF NOT EXISTS `{config.DatabaseName}`;");
                    await writer.WriteLineAsync($"USE `{config.DatabaseName}`;");
                    await writer.WriteLineAsync();

                    // 备份每个表
                    foreach (var table in tables)
                    {
                        // 表结构
                        using (var cmd = new MySqlCommand($"SHOW CREATE TABLE `{table}`", connection))
                        {
                            using (var reader = await cmd.ExecuteReaderAsync())
                            {
                                if (await reader.ReadAsync())
                                {
                                    await writer.WriteLineAsync($"-- 表结构: {table}");
                                    await writer.WriteLineAsync($"{reader.GetString(1)};");
                                    await writer.WriteLineAsync();
                                }
                            }
                        }

                        // 表数据
                        using (var cmd = new MySqlCommand($"SELECT * FROM `{table}`", connection))
                        {
                            using (var reader = await cmd.ExecuteReaderAsync())
                            {
                                var schemaTable = reader.GetSchemaTable();
                                if (schemaTable != null)
                                {
                                    var columnNames = schemaTable.Rows.Cast<DataRow>()
                                        .Select(row => row["ColumnName"].ToString())
                                        .ToList();

                                    if (columnNames.Count > 0)
                                    {
                                        var data = new List<List<object>>();

                                        while (await reader.ReadAsync())
                                        {
                                            var row = new List<object>();
                                            for (int i = 0; i < columnNames.Count; i++)
                                            {
                                                row.Add(reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i));
                                            }
                                            data.Add(row);
                                        }

                                        if (data.Count > 0)
                                        {
                                            await writer.WriteLineAsync($"-- 表数据: {table}");
                                            await writer.WriteLineAsync($"INSERT INTO `{table}` ({string.Join(", ", columnNames.Select(c => $"`{c}`"))}) VALUES");

                                            for (int i = 0; i < data.Count; i++)
                                            {
                                                var values = data[i].Select(val => 
                                                    val is DBNull ? "NULL" :
                                                    val is string ? $"'" + MySqlHelper.EscapeString(val.ToString() ?? "") + "'" :
                                                    val is DateTime dt ? $"'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'" :
                                                    val.ToString()
                                                ).ToArray();

                                                await writer.WriteLineAsync($"  ({string.Join(", ", values)}){(i < data.Count - 1 ? "," : ";")}");
                                            }
                                            await writer.WriteLineAsync();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 备份PostgreSQL数据库
        /// </summary>
        private async Task<string> BackupPostgreSQLAsync(BackupSchedule schedule)
        {
            var config = schedule.DbConfig;
            string backupFileName;
            string backupFilePath;

            // 优先使用自定义备份文件名
            if (!string.IsNullOrEmpty(schedule.CustomBackupFileName))
            {
                backupFileName = schedule.CustomBackupFileName;
                // 确保文件扩展名为.sql
                if (!backupFileName.EndsWith(".sql", StringComparison.OrdinalIgnoreCase))
                {
                    backupFileName += ".sql";
                }
            }
            else
            {
                // 如果没有自定义文件名，使用原来的逻辑生成文件名
                string timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
                backupFileName = $"{config.DatabaseName}_backup_{timestamp}.sql";
            }
            
            backupFilePath = Path.Combine(schedule.BackupDirectory, backupFileName);

            try
            {
                // 设置环境变量（用于pg_dump命令）
                Environment.SetEnvironmentVariable("PGPASSWORD", config.Password);

                // 创建pg_dump命令
                string args = $"--host={config.Server} --port={config.Port} --username={config.Username} --dbname={config.DatabaseName} --file={backupFilePath}";

                if (!string.IsNullOrEmpty(config.AdditionalParams))
                {
                    args += " " + config.AdditionalParams;
                }

                // 执行pg_dump命令
                await ExecuteCommandAsync("pg_dump", args);

                // 如果没有生成备份文件，尝试使用代码方式备份
                if (!File.Exists(backupFilePath))
                {
                    _logger.LogInformation("pg_dump命令未找到，尝试使用代码方式备份PostgreSQL数据库");
                    await BackupPostgreSQLByCodeAsync(config, backupFilePath);
                }

                return backupFilePath;
            }
            finally
            {
                // 清除环境变量
                Environment.SetEnvironmentVariable("PGPASSWORD", null);
            }
        }

        /// <summary>
        /// 使用代码方式备份PostgreSQL数据库
        /// </summary>
        private async Task BackupPostgreSQLByCodeAsync(DbConnectionConfig config, string backupFilePath)
        {
            string connectionString = $"Host={config.Server};Port={config.Port};Username={config.Username};Password={config.Password};Database={config.DatabaseName};";

            if (!string.IsNullOrEmpty(config.AdditionalParams))
            {
                connectionString += config.AdditionalParams;
            }

            using (var connection = new NpgsqlConnection(connectionString))
            {
                await connection.OpenAsync();

                // 获取所有表
                var tables = new List<string>();
                using (var cmd = new NpgsqlCommand("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'", connection))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            tables.Add(reader.GetString(0));
                        }
                    }
                }

                using (var writer = new StreamWriter(backupFilePath))
                {
                    // 创建数据库语句
                    await writer.WriteLineAsync($"CREATE DATABASE {config.DatabaseName};");
                    await writer.WriteLineAsync($"\\connect {config.DatabaseName};");
                    await writer.WriteLineAsync();

                    // 备份每个表
                    foreach (var table in tables)
                    {
                        // 表结构
                        using (var cmd = new NpgsqlCommand($"pg_get_tabledef('{table}')", connection))
                        {
                            var createTableScript = await cmd.ExecuteScalarAsync() as string;
                            if (!string.IsNullOrEmpty(createTableScript))
                            {
                                await writer.WriteLineAsync($"-- 表结构: {table}");
                                await writer.WriteLineAsync(createTableScript + ";");
                                await writer.WriteLineAsync();
                            }
                        }

                        // 表数据
                        using (var cmd = new NpgsqlCommand($"SELECT * FROM {table}", connection))
                        {
                            using (var reader = await cmd.ExecuteReaderAsync())
                            {
                                var schemaTable = reader.GetSchemaTable();
                                if (schemaTable != null)
                                {
                                    var columnNames = schemaTable.Rows.Cast<DataRow>()
                                        .Select(row => row["ColumnName"].ToString())
                                        .ToList();

                                    if (columnNames.Count > 0)
                                    {
                                        var data = new List<List<object>>();

                                        while (await reader.ReadAsync())
                                        {
                                            var row = new List<object>();
                                            for (int i = 0; i < columnNames.Count; i++)
                                            {
                                                row.Add(reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i));
                                            }
                                            data.Add(row);
                                        }

                                        if (data.Count > 0)
                                        {
                                            await writer.WriteLineAsync($"-- 表数据: {table}");
                                            await writer.WriteLineAsync($"INSERT INTO {table} ({string.Join(", ", columnNames)}) VALUES");

                                            for (int i = 0; i < data.Count; i++)
                                            {
                                                var values = data[i].Select(val => 
                                                    val is DBNull ? "NULL" :
                                                    val is string ? $"'" + (val.ToString() ?? "").Replace("'", "''") + "'" :
                                                    val is DateTime dt ? $"'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'" :
                                                    val.ToString()
                                                ).ToArray();

                                                await writer.WriteLineAsync($"  ({string.Join(", ", values)}){(i < data.Count - 1 ? "," : ";")}");
                                            }
                                            await writer.WriteLineAsync();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 备份SQLite数据库
        /// </summary>
        private async Task<string> BackupSQLiteAsync(BackupSchedule schedule)
        {
            var config = schedule.DbConfig;
            string backupFileName;
            string backupFilePath;

            // 优先使用自定义备份文件名
            if (!string.IsNullOrEmpty(schedule.CustomBackupFileName))
            {
                backupFileName = schedule.CustomBackupFileName;
                // 确保文件扩展名为.db
                if (!backupFileName.EndsWith(".db", StringComparison.OrdinalIgnoreCase))
                {
                    backupFileName += ".db";
                }
            }
            else
            {
                // 如果没有自定义文件名，使用原来的逻辑生成文件名
                string timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
                backupFileName = $"{Path.GetFileNameWithoutExtension(config.SqliteFilePath)}_backup_{timestamp}.db";
            }
            
            backupFilePath = Path.Combine(schedule.BackupDirectory, backupFileName);

            try
            {
                // 对于SQLite，我们可以直接复制数据库文件（在非锁定状态下）
                if (File.Exists(config.SqliteFilePath))
                {
                    // 先尝试直接复制
                    try
                    {
                        File.Copy(config.SqliteFilePath, backupFilePath, true);
                    }
                    catch (IOException)
                    {
                        // 如果文件被锁定，使用SQLite的备份API
                        _logger.LogInformation("SQLite数据库文件被锁定，尝试使用SQLite备份API");
                        await BackupSQLiteByApiAsync(config.SqliteFilePath, backupFilePath);
                    }
                }
                else
                {
                    throw new FileNotFoundException("SQLite数据库文件不存在", config.SqliteFilePath);
                }

                return backupFilePath;
            }
            catch (Exception ex)
            {
                _logger.LogError("SQLite备份失败: {ErrorMessage}", ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 使用SQLite API备份数据库
        /// </summary>
        private async Task BackupSQLiteByApiAsync(string sourceFilePath, string destinationFilePath)
        {
            // 确保目标文件不存在
            if (File.Exists(destinationFilePath))
            {
                File.Delete(destinationFilePath);
            }

            // SQLite连接字符串
            string sourceConnectionString = $"Data Source={sourceFilePath};Version=3;";
            string destinationConnectionString = $"Data Source={destinationFilePath};Version=3;";

            // 使用SQLite的备份API
            using (var sourceConn = new SQLiteConnection(sourceConnectionString))
            using (var destConn = new SQLiteConnection(destinationConnectionString))
            {
                await sourceConn.OpenAsync();
                await destConn.OpenAsync();

                // 执行备份命令
                using (var command = new SQLiteCommand("VACUUM INTO @destination", sourceConn))
                {
                    command.Parameters.AddWithValue("@destination", destinationFilePath);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 备份SQL Server数据库
        /// </summary>
        private async Task<string> BackupSQLServerAsync(BackupSchedule schedule)
        {
            var config = schedule.DbConfig;
            string backupFileName;
            string backupFilePath;

            // 优先使用自定义备份文件名
            if (!string.IsNullOrEmpty(schedule.CustomBackupFileName))
            {
                backupFileName = schedule.CustomBackupFileName;
                // 确保文件扩展名为.bak
                if (!backupFileName.EndsWith(".bak", StringComparison.OrdinalIgnoreCase))
                {
                    backupFileName += ".bak";
                }
            }
            else
            {
                // 如果没有自定义文件名，使用原来的逻辑生成文件名
                string timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
                backupFileName = $"{config.DatabaseName}_backup_{timestamp}.bak";
            }
            
            backupFilePath = Path.Combine(schedule.BackupDirectory, backupFileName);

            try
            {
                // 根据是否提供了用户名和密码来构建不同的连接字符串
                string connectionString;

                if (string.IsNullOrEmpty(config.Username) && string.IsNullOrEmpty(config.Password))
                {
                    // Windows身份验证连接字符串（不包含用户名和密码）
                    if (config.Port > 0)
                    {
                        connectionString = $"Server={config.Server},{config.Port};Database=master;Integrated Security=True;";
                    }
                    else
                    {
                        connectionString = $"Server={config.Server};Database=master;Integrated Security=True;";
                    }
                }
                else
                {
                    // SQL Server身份验证连接字符串
                    if (config.Port > 0)
                    {
                        connectionString = $"Server={config.Server},{config.Port};Database=master;User Id={config.Username};Password={config.Password};";
                    }
                    else
                    {
                        connectionString = $"Server={config.Server};Database=master;User Id={config.Username};Password={config.Password};";
                    }
                }

                // 添加额外的连接参数，确保前面有分号
                if (!string.IsNullOrEmpty(config.AdditionalParams))
                {
                    if (config.AdditionalParams.StartsWith(';'))
                    {
                        connectionString += config.AdditionalParams;
                    }
                    else
                    {
                        connectionString += $";{config.AdditionalParams}";
                    }
                }

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 创建备份命令
                    string backupCommand = $"BACKUP DATABASE [{config.DatabaseName}] TO DISK = '{backupFilePath.Replace("'", "''")}' WITH FORMAT, INIT, NAME = N'{config.DatabaseName} 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10";

                    using (var command = new SqlCommand(backupCommand, connection))
                    {
                        command.CommandTimeout = 3600; // 1小时超时
                        await command.ExecuteNonQueryAsync();
                    }
                }

                return backupFilePath;
            }
            catch (Exception ex)
            {
                _logger.LogError("SQL Server备份失败: {ErrorMessage}", ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 备份Access数据库
        /// </summary>
        private async Task<string> BackupAccessAsync(BackupSchedule schedule)
        {
            var config = schedule.DbConfig;
            string backupFileName;
            string backupFilePath;

            // 优先使用自定义备份文件名
            if (!string.IsNullOrEmpty(schedule.CustomBackupFileName))
            {
                backupFileName = schedule.CustomBackupFileName;
                // 确保文件有正确的扩展名
                string extension = Path.GetExtension(backupFileName);
                if (string.IsNullOrEmpty(extension))
                {
                    // 如果没有扩展名，使用原数据库文件的扩展名
                    extension = Path.GetExtension(config.AccessFilePath);
                    backupFileName += extension;
                }
            }
            else
            {
                // 如果没有自定义文件名，使用原来的逻辑生成文件名
                string timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
                backupFileName = $"{Path.GetFileNameWithoutExtension(config.AccessFilePath)}_backup_{timestamp}{Path.GetExtension(config.AccessFilePath)}";
            }
            
            backupFilePath = Path.Combine(schedule.BackupDirectory, backupFileName);

            try
            {
                // 记录要备份的Access文件路径信息
                _logger.LogDebug("准备备份Access数据库，文件路径: {FilePath}", config.AccessFilePath);

                // 验证Access文件路径是否为空
                if (string.IsNullOrEmpty(config.AccessFilePath))
                {
                    throw new ArgumentNullException("AccessFilePath", "Access数据库文件路径不能为空");
                }

                // 对于Access，我们可以直接复制数据库文件（在非锁定状态下）
                if (File.Exists(config.AccessFilePath))
                {
                    _logger.LogInformation("找到Access数据库文件: {FilePath}", config.AccessFilePath);

                    // 确保备份目录存在
                    Directory.CreateDirectory(schedule.BackupDirectory);

                    // 先尝试直接复制
                    try
                    {
                        File.Copy(config.AccessFilePath, backupFilePath, true);
                        _logger.LogInformation("Access数据库备份成功，保存至: {BackupFilePath}", backupFilePath);
                    }
                    catch (IOException ex)
                    {
                        // 如果文件被锁定，使用Access的压缩和修复功能
                        _logger.LogInformation("Access数据库文件被锁定，尝试使用Access压缩和修复功能: {ErrorMessage}", ex.Message);
                        await BackupAccessByCompactionAsync(config.AccessFilePath, backupFilePath);
                    }
                }
                else
                {
                    _logger.LogError("Access数据库文件不存在: {FilePath}。请检查配置文件中的AccessFilePath设置是否正确。", config.AccessFilePath);
                    throw new FileNotFoundException($"Access数据库文件不存在: {config.AccessFilePath}", config.AccessFilePath);
                }

                return backupFilePath;
            }
            catch (Exception ex)
            {
                _logger.LogError("Access备份失败: {ErrorMessage}", ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 使用Access压缩和修复功能备份数据库
        /// </summary>
        private async Task BackupAccessByCompactionAsync(string sourceFilePath, string destinationFilePath)
        {
            try
            {
                // 使用OleDb连接和Access的压缩功能
                // 注意：这种方法需要安装Access数据库引擎
                string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={sourceFilePath}";

                using (var connection = new OleDbConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 压缩数据库到目标文件
                    // 注意：这种方法需要Microsoft.Office.Interop.Access或其他Access互操作库
                    // 这里提供的是一个简化版本，实际使用时可能需要引用额外的库
                    throw new NotSupportedException("Access压缩和修复功能需要安装Microsoft Access数据库引擎并引用相应的互操作库");
                }
            }
            catch (Exception ex)
            {
                _logger.LogError("Access压缩和修复失败: {ErrorMessage}", ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 执行外部命令
        /// </summary>
        private async Task ExecuteCommandAsync(string command, string arguments)
        {
            var processStartInfo = new ProcessStartInfo
            {
                FileName = command,
                Arguments = arguments,
                RedirectStandardOutput = true,
                RedirectStandardError = true,
                UseShellExecute = false,
                CreateNoWindow = true
            };

            using (var process = new Process { StartInfo = processStartInfo })
            {
                process.OutputDataReceived += (sender, e) =>
                {
                    if (!string.IsNullOrEmpty(e.Data))
                    {
                        _logger.LogDebug("{Command}输出: {OutputData}", command, e.Data);
                    }
                };

                process.ErrorDataReceived += (sender, e) =>
                {
                    if (!string.IsNullOrEmpty(e.Data))
                    {
                        _logger.LogWarning("{Command}错误: {ErrorData}", command, e.Data);
                    }
                };

                process.Start();
                process.BeginOutputReadLine();
                process.BeginErrorReadLine();

                await process.WaitForExitAsync();

                if (process.ExitCode != 0)
                {
                    throw new Exception($"命令执行失败: {command} {arguments}, 退出代码: {process.ExitCode}");
                }
            }
        }

        /// <summary>
        /// 检查磁盘空间
        /// </summary>
        public bool CheckDiskSpace(string directoryPath, int thresholdMB)
        {
            try
            {
                // 获取驱动器信息
                string driveLetter = Path.GetPathRoot(directoryPath) ?? @"C:\";
                DriveInfo drive = new DriveInfo(driveLetter);

                // 计算可用空间（MB）
                long availableSpaceMB = drive.AvailableFreeSpace / (1024 * 1024);

                // 将日志级别从Debug改为Info，以便用户能看到实际的磁盘空间信息
                _logger.LogInformation("驱动器 {driveLetter} 可用空间: {availableSpaceMB} MB, 阈值: {thresholdMB} MB", driveLetter, availableSpaceMB, thresholdMB);

                // 如果可用空间小于阈值，返回true（空间不足）
                return availableSpaceMB < thresholdMB;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "检查磁盘空间失败: {Message}", ex.Message);
                // 出错时默认认为空间足够
                return false;
            }
        }

        /// <summary>
        /// 清理旧备份文件
        /// </summary>
        public async Task CleanupOldBackupsAsync(BackupSchedule schedule)
        {
            await Task.Run(() =>
            {
                try
                {
                    if (!Directory.Exists(schedule.BackupDirectory))
                    {
                        return;
                    }

                    _logger.LogInformation("开始清理旧备份文件，保留最近 {DaysToKeep} 天的备份", schedule.AutoDeleteOlderThanDays);

                    // 获取所有备份文件并按创建时间排序
                    var files = Directory.GetFiles(schedule.BackupDirectory)
                        .Select(f => new FileInfo(f))
                        .OrderBy(f => f.CreationTime)
                        .ToList();

                    // 计算要删除的日期阈值
                    DateTime cutoffDate = DateTime.Now.AddDays(-schedule.AutoDeleteOlderThanDays);

                    // 删除过期的备份文件
                    int deletedCount = 0;
                    foreach (var file in files)
                    {
                        if (file.CreationTime < cutoffDate)
                        {
                            File.Delete(file.FullName);
                            _logger.LogInformation("已删除旧备份文件: {FileName}", file.FullName);
                            deletedCount++;
                        }
                    }

                    // 如果删除后空间仍然不足，删除最早的备份文件
                    while (CheckDiskSpace(schedule.BackupDirectory, schedule.DiskSpaceThresholdMB) && files.Count > deletedCount)
                    {
                        var oldestFile = files[deletedCount];
                        File.Delete(oldestFile.FullName);
                        _logger.LogWarning("空间仍然不足，已删除最早的备份文件: {FileName}", oldestFile.FullName);
                        deletedCount++;
                    }

                    _logger.LogInformation("清理旧备份文件完成，共删除 {DeletedCount} 个文件", deletedCount);
                }
                catch (Exception ex)
                {
                    _logger.LogError("清理旧备份文件失败: {ErrorMessage}", ex.Message);
                }
            });
        }

        /// <summary>
        /// 创建备份目录
        /// </summary>
        public void CreateBackupDirectory(string path)
        {
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
                _logger.LogInformation("创建备份目录: {Path}", path);
            }
        }

        /// <summary>
        /// 解析MySQL连接字符串
        /// </summary>
        private void ParseMySqlConnectionString(string connectionString, DbConnectionConfig dbConfig)
        {
            var builder = new MySqlConnectionStringBuilder(connectionString);
            dbConfig.Server = builder.Server;
            dbConfig.Port = (int)builder.Port;
            dbConfig.DatabaseName = builder.Database;
            dbConfig.Username = builder.UserID;
            dbConfig.Password = builder.Password;
        }

        /// <summary>
        /// 解析SQL Server连接字符串
        /// </summary>
        private void ParseSqlServerConnectionString(string connectionString, DbConnectionConfig dbConfig)
        {
            var builder = new SqlConnectionStringBuilder(connectionString);
            dbConfig.Server = builder.DataSource;
            dbConfig.DatabaseName = builder.InitialCatalog;
            dbConfig.Username = builder.UserID;
            dbConfig.Password = builder.Password;
            // 提取端口号
            if (dbConfig.Server.Contains(","))
            {
                var parts = dbConfig.Server.Split(',');
                dbConfig.Server = parts[0];
                if (int.TryParse(parts[1], out int port))
                {
                    dbConfig.Port = port;
                }
            }
        }

        /// <summary>
        /// 解析PostgreSQL连接字符串
        /// </summary>
        private void ParsePostgreSqlConnectionString(string connectionString, DbConnectionConfig dbConfig)
        {
            var builder = new NpgsqlConnectionStringBuilder(connectionString);
            dbConfig.Server = builder.Host ?? "localhost";
            dbConfig.Port = builder.Port;
            dbConfig.DatabaseName = builder.Database ?? string.Empty;
            dbConfig.Username = builder.Username ?? string.Empty;
            dbConfig.Password = builder.Password ?? string.Empty;
        }

        /// <summary>
        /// 解析SQLite连接字符串
        /// </summary>
        private void ParseSqliteConnectionString(string connectionString, DbConnectionConfig dbConfig)
        {
            // 如果 connectionString 只包含文件路径（不含等号），自动补全为标准格式
            if (!string.IsNullOrWhiteSpace(connectionString) && !connectionString.Contains("="))
            {
                dbConfig.SqliteFilePath = connectionString;
            }
            else
            {
                // 标准格式
                var builder = new SQLiteConnectionStringBuilder(connectionString);
                dbConfig.SqliteFilePath = builder.DataSource;
            }
        }

        /// <summary>
        /// 解析Access连接字符串
        /// </summary>
        private void ParseAccessConnectionString(string connectionString, DbConnectionConfig dbConfig)
        {
    // 如果 connectionString 只包含文件路径（不含等号），直接赋值
    if (!string.IsNullOrWhiteSpace(connectionString) && !connectionString.Contains("="))
    {
        dbConfig.AccessFilePath = connectionString;
    }
    else if (connectionString.Contains("Data Source="))
    {
        int startIndex = connectionString.IndexOf("Data Source=") + "Data Source=".Length;
        int endIndex = connectionString.IndexOf(";", startIndex);
        if (endIndex > startIndex)
        {
            dbConfig.AccessFilePath = connectionString.Substring(startIndex, endIndex - startIndex);
        }
        else
        {
            // 没有分号，说明Data Source在末尾，直接取到结尾
            dbConfig.AccessFilePath = connectionString.Substring(startIndex).Trim();
        }
    }
    else
    {
        // 兜底：无法识别的格式，直接赋值
        dbConfig.AccessFilePath = connectionString;
    }
        }

        /// <summary>
        /// 测试数据库连接   
        /// </summary>
        /// <param name="databaseType"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public Task<bool> TestConnectionAsync(string databaseType, string connectionString)
        {
            try
            {
                _logger.LogInformation("测试数据库连接: 类型={DatabaseType}", databaseType);

                // 根据数据库类型创建连接
                IDbConnection? connection = null;
                
                switch (databaseType.ToLower())
                {
                    case "sqlserver":
                        connection = new Microsoft.Data.SqlClient.SqlConnection(connectionString);
                        break;
                    case "mysql":
                        connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
                        break;
                    case "postgresql":
                        connection = new Npgsql.NpgsqlConnection(connectionString);
                        break;
                    case "sqlite":
                        connection = new System.Data.SQLite.SQLiteConnection(connectionString);
                        break;
                    case "access":
                        connection = new System.Data.OleDb.OleDbConnection(connectionString);
                        break;
                    default:
                        _logger.LogError("不支持的数据库类型: {DatabaseType}", databaseType);
                        return Task.FromResult(false);
                }

                // 测试连接
                connection.Open();
                connection.Close();

                _logger.LogInformation("数据库连接测试成功");
                return Task.FromResult(true);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "数据库连接测试失败: {ErrorMessage}", ex.Message);
                return Task.FromResult(false);
            }
        }

        /// <summary>
        /// 数据库类型列表
        /// </summary>
        public List<string> GetSupportedDatabaseTypes()
        {
            return new List<string> { "SQLServer", "MySQL", "PostgreSQL", "SQLite", "Access" };
        }

        /// <summary>
        /// 生成默认的备份文件路径
        /// </summary>
        /// <param name="basePath">基础路径</param>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="databaseType">数据库类型</param>
        /// <returns>生成的备份文件路径</returns>
        public string GenerateBackupFilePath(string databaseType, string databaseName, string basePath)
        {
            // 替换数据库名称中的非法字符
            var safeDbName = string.Join("_", databaseName.Split(Path.GetInvalidFileNameChars()));
            
            // 生成带时间戳的文件名
            var timestamp = DateTime.Now.ToString("yyyyMMdd_HHmmss");
            var extension = databaseType.ToLower() switch
            {
                "sqlserver" => ".bak",
                "mysql" => ".sql",
                "postgresql" => ".dump",
                "sqlite" => ".sqlite",
                "access" => ".accdb",
                _ => ".bak"
            };
            
            // 确保基础路径存在
            if (!Directory.Exists(basePath))
            {
                Directory.CreateDirectory(basePath);
            }
            
            return Path.Combine(basePath, $"{safeDbName}_{timestamp}{extension}");
        }
    }
}